package com.zygh.policeasset.mapper;

import com.zygh.policeasset.pojo.Project;
import com.zygh.policeasset.pojo.ProjectNameType;
import com.zygh.policeasset.pojo.Tender;
import java.util.List;

import org.apache.ibatis.annotations.*;

@Mapper
public interface TenderMapper {
    @Select({"<script>"+
            "select t.*,p.name as project_name,af.app_status,af.approval_steps_id as approvalStepsId," +
            "u.name as userName from tender t left join project p on p.project_no = t.project_no " +
            "left join audit_flow af on t.flow_no = af.flow_no " +
            "left join user u on af.applicant = u.id " +
            "<where><if test='name != null and name.length &gt; 0'>and p.name like '%${name}%' </if>" +
            "<if test='category != null and category &gt; 0'>and p.category=#{category} </if>" +
            "<if test='time1 != null and time2 != null and time1.length &gt; 0 and time2.length &gt; 0'>and t.start_datetime between #{time1} and #{time2} </if>" +
            "<if test='userId != null '> and af.applicant=#{userId} </if>" +
            "<if test=' status != null'> and af.app_status = #{status} </if>" +
            "</where>order by t.id desc" +
            " </script>"})
    @Results(id = "map", value = {@Result(property = "projectNo", column = "project_no"),
            @Result(property = "projectName", column = "project_name"),
            @Result(property = "companyApply", column = "company_apply"),
            @Result(property = "appCase", column = "app_case"),
            @Result(property = "projectOpinion", column = "project_opinion"),
            @Result(property = "companyOpinion", column = "company_opinion"),
            @Result(property = "companyDesign", column = "company_design"),
            @Result(property = "startDatetime", column = "start_datetime"),
            @Result(property = "flowNo", column = "flow_no"),
            @Result(property = "divisionSections", column = "division_sections"),
            @Result(property = "appStatus", column = "app_status")})
    List<Tender> findAll(@Param("name") String name,
                         @Param("status") Integer status,
                         @Param("time1") String time1,
                         @Param("time2") String time2,
                         @Param("category") Integer category,
                         @Param("userId") Integer userId);

    @Select({"select count(*) from tender t  left join audit_flow af on t.flow_no = af.flow_no where af.app_status = 1"})
    Integer findCount();

    @Select({"select t.*,p.name as project_name,pr.status as status,pr.opinion as opinion from tender t left join processrecord pr on t.id=pr.project_id and pr.process_name='tender' left join project p on p.project_no = t.project_no where pr.status=#{status} order by t.id desc "})
    @Results({@Result(property = "projectNo", column = "project_no"), @Result(property = "projectName", column = "project_name"), @Result(property = "companyApply", column = "company_apply"), @Result(property = "appCase", column = "app_case"), @Result(property = "projectOpinion", column = "project_opinion"), @Result(property = "companyOpinion", column = "company_opinion"), @Result(property = "companyDesign", column = "company_design"), @Result(property = "startDatetime", column = "start_datetime"), @Result(property = "flowNo", column = "flow_no"), @Result(property = "appStatus", column = "app_status")})
    List<Tender> findByStatus(int i);

    @Select({"select t.*,p.name as project_name,af.app_status from tender t left join project p on p.project_no = t.project_no left join audit_flow af on t.flow_no = af.flow_no where t.project_no=#{no}  order by t.id desc "})
    @Results({@Result(property = "projectNo", column = "project_no"),
            @Result(property = "projectName", column = "project_name"),
            @Result(property = "companyApply", column = "company_apply"),
            @Result(property = "appCase", column = "app_case"),
            @Result(property = "projectOpinion", column = "project_opinion"),
            @Result(property = "companyOpinion", column = "company_opinion"),
            @Result(property = "companyDesign", column = "company_design"),
            @Result(property = "startDatetime", column = "start_datetime"),
            @Result(property = "appStatus", column = "app_status")})
    List<Tender> findByProject(String str);

    @Select({"select p.*,c.type as type,c.name as categoryName,af.app_status as status from project p left join category c on p.category = c.id left join tender t on p.project_no = t.project_no left join audit_flow af on p.flow_no = af.flow_no WHERE af.app_status = 2 and p.pid != 0 and t.project_no is null and c.id not in (4) order by p.id desc "})
    @Results({@Result(property = "projectNo", column = "project_no"), @Result(property = "commissionNo", column = "commission_no"), @Result(property = "companyApply", column = "company_apply"), @Result(property = "capitalSource", column = "capital_source"), @Result(property = "projectBase", column = "project_base"), @Result(property = "startDatetime", column = "start_datetime")})
    List<Project> getAdd();

    @Insert({"insert into tender (project_no,company_apply,app_case,project_opinion,method,company_opinion," +
            "company_design,budget,capital,contactor,department,start_datetime,flow_no,division_sections) " +
            "values (#{projectNo},#{companyApply},#{appCase},#{projectOpinion},#{method},#{companyOpinion}," +
            "#{companyDesign},#{budget},#{capital},#{contactor},#{department},#{startDatetime},#{flowNo},#{divisionSections})"})
    @Options(useGeneratedKeys = true, keyProperty = "id", keyColumn = "id")
    Integer add(Tender tender);

    @Update({"update tender set project_no=#{projectNo},company_apply=#{companyApply}," +
            "app_case=#{appCase},project_opinion=#{projectOpinion},method=#{method}," +
            "company_opinion=#{companyOpinion},company_design=#{companyDesign}," +
            "budget=#{budget},capital=#{capital},contactor=#{contactor}," +
            "department=#{department},start_datetime=#{startDatetime},division_sections=#{divisionSections} " +
            "where id=#{id}"})
    Integer update(Tender tender);

    @Update({"update tender set app_status=#{status} where id=#{id}"})
    Integer updateStatus(int i, int i2);

    @Delete({"delete from tender where id=#{id}"})
    Integer remove(int i);

    @Select({"SELECT p.name,p.project_no  FROM tender t " +
            "LEFT JOIN  project p ON t.project_no= p.project_no " +
            "left join audit_flow a on t.flow_no=a.flow_no WHERE a.app_status = 2 "})
    @Results({@Result(property = "projectNo", column = "project_no"),
            @Result(property = "projectName", column = "name")})
    List<ProjectNameType> selectBytype();

    @Select({"<script>SELECT t.*,p.name,c.`name`  FROM tender t LEFT JOIN  project p ON t.project_no= p.project_no LEFT JOIN category c ON p.category = c.id left join audit_flow af on t.flow_no = af.flow_no <where>  af.app_status = 2 and p.category=#{id} <if test='name != null and name.length &gt; 0'> and p.name like '%${name}%' </if><if test='time1 != null and time2 != null and time1.length &gt; 0 and time2.length &gt; 0'> and t.start_datetime between #{time1} and #{time2} </if></where> </script>"})
    @Results({@Result(property = "projectNo", column = "project_no"), @Result(property = "projectName", column = "project_name"), @Result(property = "companyApply", column = "company_apply"), @Result(property = "appCase", column = "app_case"), @Result(property = "projectOpinion", column = "project_opinion"), @Result(property = "companyOpinion", column = "company_opinion"), @Result(property = "companyDesign", column = "company_design"), @Result(property = "startDatetime", column = "start_datetime")})
    List<Tender> toExcel(Integer num, String str, String str2, String str3);

    @Select({"<script>"+
            "select t.*,p.name as project_name,af.app_status," +
            "af.approval_steps_id as approvalStepsId,u.name as userName " +
            "from tender t left join project p on p.project_no = t.project_no " +
            "left join audit_flow af on t.flow_no = af.flow_no " +
            "left join user u on af.applicant = u.id " +
            "<where>" +
            "<if test='id != null'>and t.id = #{id} </if>" +
            "</where>" +
            "</script>"})
    @ResultMap({"map"})
    Tender findById(@Param("id") Integer num);

}